/*
 * nlsync
 * Copyright (c) 2010 apechurin 
 * 
 * Permission is hereby granted, free of charge, to any person
 * obtaining a copy of this software and associated documentation
 * files (the "Software"), to deal in the Software without
 * restriction, including without limitation the rights to use,
 * copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the
 * Software is furnished to do so, subject to the following
 * conditions:
 *
 * The above copyright notice and this permission notice shall be
 * included in all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
 * OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
 * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
 * HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
 * WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
 * OTHER DEALINGS IN THE SOFTWARE.
 */
using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using Excel = Microsoft.Office.Interop.Excel;

namespace nlsync.Xls
{
    /// <summary>
    /// Contains some useful Excel related functions.
    /// </summary>
    public class XlsUtil
    {
        /// <summary>
        /// Gets the last used cell in the column A.
        /// </summary>
        /// <param name="sheet">The sheet.</param>
        public static Excel.Range GetLastCell(Excel.Worksheet sheet)
        {
            return sheet.get_Range("a65536", Type.Missing).
                get_End(Excel.XlDirection.xlUp);
        }

        /// <summary>
        /// Type of Find() method callback. It's called for each found cell.
        /// </summary>
        /// <param name="c">The next cell range.</param>
        /// <returns>true to continue find</returns>
        public delegate bool FindCallback(Excel.Range c);

        /// <summary>
        /// Finds a string value in a specified range.
        /// </summary>
        /// <param name="range">The range to find in.</param>
        /// <param name="what">What to find.</param>
        /// <param name="findCallback">A callback to call for each match.</param>
        /// <returns>true if something was found, false otherwise</returns>
        public static bool Find(Excel.Range range, string what, 
            FindCallback findCallback)
        {
            bool res = false;

            Excel.Range c = range.Find(
                what,
                Type.Missing,
                Excel.XlFindLookIn.xlValues,
                Excel.XlLookAt.xlWhole,
                Excel.XlSearchOrder.xlByColumns,
                Excel.XlSearchDirection.xlNext,
                false,
                false,
                false);
            if (c != null)
            {
                res = true;

                string firstAddress = XlsUtil.GetRangeAddress(c);
                do
                {
                    if (!findCallback(c))
                        break;
                    c = range.FindNext(c);
                }
                while (c != null && firstAddress != XlsUtil.GetRangeAddress(c));
            }

            return res;
        }

        /// <summary>
        /// Gets a range address.
        /// </summary>
        public static string GetRangeAddress(Excel.Range c)
        {
            return c.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1,
                Type.Missing, Type.Missing);
        }

        /// <summary>
        /// Gets the cell row index.
        /// </summary>
        /// <param name="c">The cell. It can be either a single cell or 
        ///     an entire row.</param>
        public static int GetCellRow(Excel.Range c)
        {
            string address = GetRangeAddress(c);

            // entire row case e.g. `$2:$2'
            Regex regex = new Regex(@"^\$(?<row>\d+):\$\d+$");
            Match match = regex.Match(address);
            if (!match.Success)
            { 
                // cell case e.g. `A4'
                regex = new Regex(@"^\$[a-zA-Z]+\$(?<row>\d+)$");
                match = regex.Match(address);
            }

            string row = match.Result("${row}");
            return int.Parse(row);
        }

        /// <summary>
        /// Gets a value of a specified cell.
        /// </summary>
        /// <param name="sheet">The sheet.</param>
        /// <param name="row">The row.</param>
        /// <param name="column">The column.</param>
        /// <returns>The value.</returns>
        public static string GetCellValue(Excel.Worksheet sheet, int row, int column)
        {
            Excel.Range range = sheet.Cells;
            Excel.Range c = (Excel.Range)range.get_Item(row, column);
            object t = c.Value2;
            return t != null ? t.ToString() : "";
        }
    }
}
